/*
 * SalesDetail_Model.java
 * Final Project Example
 * Created on Oct 5, 2014, 03:46:54 PM
 * by Fairul Filza with Team FA_PemrogrammanLanjut
 */

package Models;

// <editor-fold defaultstate="collapsed" desc="Import Code">
import Database.DvdsRetail_Connection;
import Interfaces.ModelsProcess_Interface;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// </editor-fold>

/** @author PemrogrammanLanjut_LABIV_TI_05~09 */
public class SalesDetail_Model implements ModelsProcess_Interface {
    private long SalesDetId;
    private int Quantity;
    private long Price;
    private Sales_Model Sales;
    private Dvds_Model Dvds;
    private DvdsRetail_Connection Connex;
    private String[][] DataRecord;

    // <editor-fold defaultstate="collapsed" desc="Constructor Code">
    public SalesDetail_Model() {
        Connex = new DvdsRetail_Connection();
        Sales = new Sales_Model();
        Dvds = new Dvds_Model();
    }

    public SalesDetail_Model(long SalesDetId, int Quantity, long Price,
            String SalesId, String DvdsId) {
        Connex = new DvdsRetail_Connection();
        Sales = new Sales_Model();
        Dvds = new Dvds_Model();
        this.SalesDetId = SalesDetId;
        this.Quantity = Quantity;
        this.Price = Price;
        this.Sales.setSalesId(SalesId);
        this.Dvds.setDvdId(DvdsId);
    }

    public SalesDetail_Model(long SalesDetId) {
        Connex = new DvdsRetail_Connection();
        Sales = new Sales_Model();
        Dvds = new Dvds_Model();
        this.SalesDetId = SalesDetId;
    }

    public SalesDetail_Model(String SalesId) {
        Connex = new DvdsRetail_Connection();
        Sales = new Sales_Model();
        Dvds = new Dvds_Model();
        this.Sales.setSalesId(SalesId);
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Getter and Setter Code">
    public Dvds_Model getDvds() {
        return Dvds;
    }

    public void setDvds(Dvds_Model Dvds) {
        this.Dvds = Dvds;
    }

    public String[][] getDataRecord() {
        return DataRecord;
    }

    public void setDataRecord(String[][] DataRecord) {
        this.DataRecord = DataRecord;
    }

    public long getPrice() {
        return Price;
    }

    public void setPrice(long Price) {
        this.Price = Price;
    }

    public int getQuantity() {
        return Quantity;
    }

    public void setQuantity(int Quantity) {
        this.Quantity = Quantity;
    }

    public Sales_Model getSales() {
        return Sales;
    }

    public void setSales(Sales_Model Sales) {
        this.Sales = Sales;
    }

    public long getSalesDetId() {
        return SalesDetId;
    }

    public void setSalesDetId(long SalesDetId) {
        this.SalesDetId = SalesDetId;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Method Code">
    @Override
    public void showDataSet() {
        String Query;
        int i;

        Connex.Connection();
        try {
            Query = "SELECT a.SalesDetId, c.DvdId, c.Title, c.Price, a.Quantity, a.Price "
                    + "FROM DvdsSalesDetail a, DvdsSales b, Dvds c "
                    + "WHERE a.SalesId = b.SalesId AND a.DvdId = c.DvdId AND a.SalesId = '" + Sales.getSalesId() + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            List.last();
            DataRecord = new String[List.getRow()][6];
            List.beforeFirst();
            i = 0;
            while (List.next()) {
                DataRecord[i][0] = List.getString(1);
                DataRecord[i][1] = List.getString(2);
                DataRecord[i][2] = List.getString(3);
                DataRecord[i][3] = List.getString(4);
                DataRecord[i][4] = List.getString(5);
                DataRecord[i][5] = List.getString(6);
                i++;
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.showDataSet() " + Ex.getMessage().toString());
        }
    }

    @Override
    public void showSelectedDataSet(String Value, String Key) {
        String Query;
        String Parse;

        Connex.Connection();
        try {
            Query = "SELECT SalesDetId, Quantity, Price, SalesId, DvdId FROM DvdsSalesDetail ";
            if(Key.equals("SalesDetId")) {
                Parse = "WHERE SalesDetId = '" + Value + "'";
            } else if(Key.equals("Quantity")) {
                Parse = "WHERE Quantity = '" + Value + "'";
            } else if(Key.equals("Price")) {
                Parse = "WHERE Price = '" + Value + "'";
            } else if(Key.equals("SalesId")) {
                Parse = "WHERE SalesId = '" + Value + "'";
            } else if(Key.equals("DvdId")) {
                Parse = "WHERE DvdId = '" + Value + "'";
            } else {
                Parse = "";
            }
            Query = Query + Parse;
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while (List.next()) {
                SalesDetId = List.getLong(1);
                Quantity = List.getInt(2);
                Price = List.getLong(3);
                Sales.setSalesId(List.getString(4));
                Dvds.setDvdId(List.getString(5));
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.showSelectedDataSet() " + Ex.getMessage().toString());
        }
    }

    @Override
    public boolean addDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "INSERT INTO DvdsSalesDetail VALUES('" + SalesDetId + "','" + Quantity + "','" + Price + "','" + Sales.getSalesId() + "','" + Dvds.getDvdId() + "')";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean editDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "UPDATE DvdsSalesDetail SET Quantity = '" + Quantity + "', Price = '" + Price + "', "
                    + "SalesId = '" + Sales.getSalesId() + "', DvdId = '" + Dvds.getDvdId() + "' "
                    + "WHERE SalesDetId = '" + SalesDetId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean subDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "DELETE FROM DvdsSalesDetail WHERE SalesDetId = '" + SalesDetId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public String genIdDataValue() {
        String Query;
        String Result;
        int hasil;

        Connex.Connection();
        try {
            Result = null;
            Query = "SELECT SalesDetId FROM DvdsSalesDetail ORDER BY SalesDetId DESC LIMIT 1";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while (List.next()) {
                Result = List.getString(1).trim();
            }
            List.close();
            Stat.close();

            if (!Result.equals("")) {
                hasil = Integer.parseInt(Result) + 1;
                Result = String.valueOf(hasil);
            } else {
                Result = "1";
            }
            return Result;
        } catch (Throwable Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.genIdDataValue() " + Ex.getMessage());
            return "1";
        }
    }

    public boolean cancelTransact(String SalesId) {
        String Query;
        String DvdIdStr;
        String[] ListCancel;
        int stock, qty, end, i, n;

        Connex.Connection();
        try {
            n = 0;
            Query = "SELECT count(c.DvdId) AS DvdId FROM DvdsSalesDetail a, DvdsSales b, Dvds c "
                    + "WHERE a.SalesId = b.SalesId AND a.DvdId = c.DvdId AND a.SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while(List.next()) n = List.getInt(1);
            //System.out.println("INDEX :------------------- " + n);
            ListCancel = new String[n];

            Query = "SELECT c.DvdId, c.Stock, a.Quantity "
                    + "FROM DvdsSalesDetail a, DvdsSales b, Dvds c "
                    + "WHERE a.SalesId = b.SalesId AND a.DvdId = c.DvdId "
                    + "AND a.SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Stat = Connex.getConnector().createStatement();
            List = Stat.executeQuery(Query);
            end = 0;
            i = 0;
            while(List.next()) {
                DvdIdStr = List.getString(1);
                stock = List.getInt(2);
                qty = List.getInt(3);
                end = stock + qty;
                ListCancel[i] = "UPDATE Dvds SET Stock = '" + end + "' WHERE DvdId = '" + DvdIdStr + "'";
                System.out.println("[QUERY] - " + ListCancel[i]);
                i++;
            }
            for(i = 0; i < n; i++) {
                Connex.getConnector().createStatement().executeUpdate(ListCancel[i]);
            }
            List.close();
            Stat.close();
            return true;
        } catch(SQLException Ex) {
            System.out.println("[ERROR] - SalesDetail_Model.cancelTransact at " + Ex.getMessage());
            return false;
        }
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Tester Code">
//    public void methodTester(int flag) {
//        switch(flag) {
//            case 0:
//                showDataSet();
//                System.out.println("Result = " + this.DataRecord[0][1]);
//                break;
//            case 1:
//                showSelectedDataSet("1", "SalesDetId");
//                System.out.println("Result = " + this.Quantity);
//                break;
//            case 2:
//                System.out.println("Result = " + addDataValue());
//                break;
//            case 3:
//                System.out.println("Result = " + editDataValue());
//                break;
//            case 4:
//                System.out.println("Result = " + subDataValue());
//                break;
//            case 5:
//                System.out.println("Result = " + genIdDataValue());
//                break;
//            case 6:
//                System.out.println("Result = " + cancelTransact("1000001"));
//                break;
//        }
//    }
//
//    public static void main(String[] args) {
//        SalesDetail_Model Me = new SalesDetail_Model(1, 5, 100000, "1000001", "0001");
////        Sales_Model Me = new Sales_Model();
//        Me.methodTester(5);
//    }
    // </editor-fold>
}
// End of Code